﻿CREATE PROCEDURE [dbo].[Report_CategorySameStoreComparison_Pareto]
	   @RangeReviewId int
	 , @CategoryId smallint
	 , @IncludePriorYear bit = 0
AS


--TODO: incluir atributos de articles
declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId)
declare @totalCoverage INT = @totalStores * @totalMonths

declare @joinsDate DATE 
IF @IncludePriorYear = 1  
	set @joinsDate = @startDatePY
ELSE 
	set @joinsDate = @startDate


--TODO: incluir atributos de articles
SELECT 
	  Data.ArticleId as Id
	, Data.Code
	, Data.Name
	, Data.Brand
	, Data.Manufacturer
	, Data.Attribute1
	, Data.Attribute2
	, Data.Attribute3
	, Data.Attribute4
	, Data.Attribute5
	, Data.Width
	, Data.Facings
	, ISNULL(Data.Width, 0) * ISNULL(Data.Facings, 0) as LinearSpace
	, Data.Delist
	, Data.DelistComment
	, Data.IsDelisted
	, Data.IsDeletedPerformance
	, Data.IsDeletedRangeReview
	, Data.IsNew
	, parentCat.Name as Category
	, cat.Name as Subcategory
	, cat.Code as MC
	, CASE WHEN umc.Id IS NOT NULL THEN ISNULL(SUM(cast(Units as money) / umc.Value) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(cast(Units as money)) / CAST(@totalCoverage AS MONEY),0) END AS Units	
	, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(Sales / (1 + Data.TaxRate) * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS Sales	
	, CASE WHEN am.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * am.Margin 
		   WHEN scm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * scm.Margin 
		   WHEN cm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * cm.Margin 
		   ELSE ISNULL(SUM(GM * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS GM	
	, ISNULL(Data.ArticlesCount / CAST(@totalCoverage AS MONEY), 0) as Coverage	
	, ISNULL(Data.StoresCount / CAST(@totalStores AS MONEY), 0) as StoreCoverage
	, ISNULL(Data.StoresCount, 0) as StoreCoverageQuantity
	, ISNULL(Data.MonthsCount / CAST(@totalMonths AS MONEY), 0) as MonthsCoverage
	, ISNULL(Data.MonthsCount, 0) as MonthsCoverageQuantity
	, ISNULL(SUM(Data.InventoryCost * ex.Rate) / CAST(@totalCoverage AS MONEY),0) as InventoryCost
	, umc.TargetUnit UOMConversionTargetUnit
	, umc.Value UOMConversionValue
	, Data.IsPriorYear
	
FROM
(
	SELECT
		ar.Id as ArticleId
		, ar.Code
		, ar.Name
		, ar.Brand
		, ar.Manufacturer
		, ar.Attribute1
		, ar.Attribute2
		, ar.Attribute3
		, ar.Attribute4
		, ar.Attribute5
		, ar.UOM
		, ar.TaxRate
		, ISNULL(ar.Width, 0) as Width
		, ISNULL(ar.Facings, 0) as Facings
		, ISNULL(ar.Width, 0) * ISNULL(ar.Facings, 0) as LinearSpace
		, ar.Delist
		, com.Description as DelistComment
		, ar.IsDelisted
		, ar.IsDeletedPerformance
		, ar.IsDeletedRangeReview
		, ar.IsNew
		, ar.CategoryId
		, ar.UnitOfMeasureConvertionId
		, SUM(sales.Units) Units
		, SUM(sales.Sales) Sales
		, SUM(sales.GM) as GM
		, SUM(inv.Cost) as InventoryCost
		, sales.Date
		, sales.StoreId		
		, Coverage.MonthsCount
		, Coverage.StoresCount
		, Coverage.ArticlesCount
		, CASE 
			WHEN sales.Date BETWEEN @startDatePY AND DATEADD(MONTH, @totalMonths - 1, @startDatePY) THEN 1
			WHEN sales.Date BETWEEN @startDate AND @endDate THEN 0
			ELSE NULL
		END AS IsPriorYear
	FROM Articles ar 
		INNER JOIN Categories cat on ar.CategoryId = cat.Id 	
		LEFT JOIN Comments com on ar.CommentId = com.Id
		LEFT JOIN ArticleSales sales on sales.ArticleId = ar.Id
			AND (sales.Date BETWEEN @joinsDate AND @endDate) 
			AND sales.Confirmed = 1 
			AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)	
		LEFT JOIN Inventories inv on inv.ArticleId = sales.ArticleId
								 and inv.Date = sales.Date
								 and inv.StoreId = sales.StoreId
		LEFT JOIN
		(
			select ar.Id, COUNT(ar.Id) as ArticlesCount, COUNT(distinct sales.StoreId) as  StoresCount, COUNT(distinct sales.Date) as MonthsCount
			FROM Articles ar
			INNER JOIN Categories cat on ar.CategoryId = cat.Id
			LEFT JOIN ArticleSales sales on sales.ArticleId = ar.Id
										AND sales.Date between @startDate and @endDate	
										AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)	
										AND sales.Confirmed = 1 
			where ar.RangeReviewId = 2 
				and @CategoryId = 0 OR cat.ParentId = @CategoryId OR cat.Id = @CategoryId
			group by 
			ar.Id
			
		) as Coverage on Coverage.Id = sales.ArticleId

							
	Where ar.RangeReviewId = @RangeReviewId
	  and (@CategoryId = 0 OR ar.CategoryId = @CategoryId OR cat.ParentId = @CategoryId) 	
	Group by
		ar.Id
		, ar.Code
		, ar.Name
		, ar.Brand
		, ar.Manufacturer
		, ar.Attribute1
		, ar.Attribute2
		, ar.Attribute3
		, ar.Attribute4
		, ar.Attribute5
		, ar.UOM
		, ar.TaxRate
		, ar.Width
		, ar.Facings
		, ar.Delist
		, com.Description
		, ar.IsDeletedRangeReview
		, ar.IsDelisted
		, ar.IsDeletedPerformance
		, ar.IsNew
		, ar.CategoryId
		, ar.UnitOfMeasureConvertionId
		, sales.Date
		, sales.StoreId
		, Coverage.MonthsCount
		, Coverage.StoresCount
		, Coverage.ArticlesCount
	
) AS Data
INNER JOIN RangeReviews rr on rr.Id = @RangeReviewId
INNER JOIN Currencies cur on rr.CountryId = cur.CountryId
INNER JOIN Countries co on rr.CountryId = co.Id
INNER JOIN Categories cat on Data.CategoryId = cat.Id 
								AND (@CategoryId = 0 OR Data.CategoryId = @CategoryId OR cat.ParentId = @CategoryId) 
INNER JOIN Categories parentCat on cat.ParentId = parentCat.Id and parentCat.ParentId = 6
LEFT OUTER JOIN CurrencyExchangeRates ex on ex.Date = Data.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
LEFT OUTER JOIN UnitOfMeasureConvertions umc on Data.UnitOfMeasureConvertionId = umc.Id and Data.UOM = umc.SourceUnit
LEFT JOIN TheoreticalMargins am  on am.CountryId = rr.CountryId AND  am.ArticleId = Data.ArticleId  
LEFT JOIN TheoreticalMargins scm on scm.CountryId = rr.CountryId AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
LEFT JOIN TheoreticalMargins cm  on cm.CountryId = rr.CountryId AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)


WHERE 
	IsPriorYear IS NOT NULL
	AND (@IncludePriorYear = 1 OR IsPriorYear = 0)
GROUP BY 
	  Data.ArticleId
	, Data.Code
	, Data.Name
	, Data.Brand
	, Data.Manufacturer
	, Data.Attribute1
	, Data.Attribute2
	, Data.Attribute3
	, Data.Attribute4
	, Data.Attribute5
	, Data.Width
	, Data.Facings
	, Data.Delist
	, Data.DelistComment
	, Data.IsDelisted
	, Data.IsDeletedPerformance
	, Data.IsDeletedRangeReview
	, Data.IsNew
	, Data.TaxRate
	, parentCat.Name 
	, cat.Name 
	, cat.Code 
	, co.IsVATIncluded
	, umc.Id
	, umc.TargetUnit
	, umc.Value
	, am.Margin
	, scm.Margin
	, cm.Margin
	, co.IsVATIncluded
	, Data.IsPriorYear	
	, Data.StoresCount
	, Data.MonthsCount
	, Data.ArticlesCount
Order By
	Sales DESC